Spreadsheets are suited for manipulating data having a textual representation that fits into a cell. Examples of such textual representations include integer, date, time, float, Boolean and strings. The resulting tabular presentation makes it possible to manipulate one-dimensional and two-dimensional arrays of these simple data types.
Information Technology (IT) systems are currently using and producing various complex data types including, but not limited to, eXtensible Markup Language (XML). New capabilities have been introduced into some spreadsheets to cope with these complex data types. For instance, Microsoft Excel® includes the following features in its most recent release that make Excel® usable with complex data.
Data connectivity uses real-time data servers to connect spreadsheet cells to underlying data sources. Through data providers, spreadsheet cell values can be dynamically updated and dependent cells can be automatically recalculated.
Data cube operator, which is referred to as Pivot Table in Excel®, is a tool that may be used to display and to manipulate multidimensional data. The tool allows a user to select row and column dimensions of a spreadsheet and to perform aggregation operation(s) (e.g., count or average) on resulting selected cells. The tool displays cell values for the selected aggregation operation(s) and enables roll-up and drill-down operations.
The list abstraction utility facilitates collection of data having various sizes. Lists represent dynamic ranges of cells, which automatically adapt to their content size. When the number of rows in a list changes, the utility automatically provide a new range value. Thus, a sum performed on a list range returns the correct sum irrespective of the number of rows in the list. A special row (the last row) stores computations that depend on the complete set of rows. This last row is also used to extend the list, i.e., new data entered in this row is added to the list resulting in an increase in the size of the list.
Microsoft Excel® also provides support for XML documents through mappings between data schema (which may be dynamically evaluated) and a worksheet. A cell of the worksheet can be associated to an XML path expression that returns a simple type value (e.g., an element containing simple types or an attribute value). The XML mapping also benefits from the list abstraction's capability to handle new input data (in its last row), which allows a user to input values that can later be exported as XML content.